iT邦幫忙

3

🗄️ Oracle 資料清理與跨庫比對的實務經驗

  • 分享至 

  • xImage
  •  

https://ithelp.ithome.com.tw/upload/images/20250808/20155103Ps2GDaxZQJ.png

✏️ 從欄位內容批次替換開始

在維護系統資料時,我遇到需要將 ATT_LEAVECODE 資料表中 REMARK 欄位的特定字串批次更新,例如把 "-阿民" 全部移除,並將 "#1234" 改成 "#6789"
最簡單的做法是使用 REPLACE() 函數:

UPDATE ATT_LEAVECODE
SET REMARK = REPLACE(REPLACE(REMARK, '-阿民', ''), '#1234', '#6789');

但為了避免誤改整張表,我會先分三步:

  1. 查詢受影響資料
SELECT * FROM ATT_LEAVECODE
WHERE REMARK LIKE '%-阿民%' OR REMARK LIKE '%#1234%';
  1. 模擬更新結果
SELECT REMARK AS OLD_REMARK,
       REPLACE(REPLACE(REMARK, '-阿民', ''), '#1234', '#6789') AS NEW_REMARK
FROM ATT_LEAVECODE
WHERE REMARK LIKE '%-阿民%' OR REMARK LIKE '%#1234%';
  1. 確認無誤後再更新並 COMMIT
UPDATE ATT_LEAVECODE
SET REMARK = REPLACE(REPLACE(REMARK, '-阿民', ''), '#1234', '#6789')
WHERE REMARK LIKE '%-阿民%' OR REMARK LIKE '%#1234%';
COMMIT;

🔍 比對兩個資料庫相同資料表的差異(變數暫存比對)

我遇到一個狀況,要比對某張資料表多出來2筆資料內容是什麼?

流程

  1. 在資料庫 A 取出清單:
SELECT MODU_ID FROM SYS_MODULE ORDER BY MODU_ID;

將結果複製下來並格式化成:

'M001', 'M002', 'M005'
  1. 在資料庫 B 找出多出來的:
SELECT MODU_ID FROM SYS_MODULE
WHERE MODU_ID NOT IN ('M001', 'M002', 'M005')
ORDER BY MODU_ID;
  1. 反向再做一次(資料庫 B → 資料庫 A),即可列出雙方差異。

💡 小技巧

  • 在 Excel 用公式快速加引號與逗號,避免手工處理。
  • 若資料量大,建議匯出成檔案後用 Notepad++ Compare 外掛或 diff 工具比對。

🛠️ Oracle 編輯器的格式化與輸出技巧

在 SQL Developer、PL/SQL Developer、Toad 等工具中:

  • 格式化 SQL 語法Ctrl + F7(SQL Developer)
  • 直接產生 IN 條件字串
SELECT 'IN (' || LISTAGG('''' || MODU_ID || '''', ', ') 
       WITHIN GROUP (ORDER BY MODU_ID) || ')' AS in_clause
FROM SYS_MODULE;
  • 匯出查詢結果:右鍵 → Export,可存成 CSV / TXT / INSERT 語法。

🧩 對照表:比對方式選擇

方法 優點 缺點 適用情境
變數暫存比對 不需權限、直接用 SQL 完成 手動複製清單、長度有限 權限受限、資料量不大

💬 推薦詢問 GPT 的 Prompt

請幫我產生一段 Oracle SQL,可以在更新資料前先查詢並顯示修改前後差異,再由我決定是否 COMMIT。


圖片
  熱門推薦
圖片
{{ item.channelVendor }} | {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言